﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

using System.Configuration;
using System.ComponentModel;


namespace Osiptel.CCPP.DL
{

    public interface IConexion
    {
        OracleConnection retConexion();
        string retStrConexion();
        void retOpen();
        void retClose();
        //string retRegEdit();
    }


    public class DL_CONEXION : IConexion, IDisposable
    {
        #region "variables"
        public OracleConnection conexion;
        private Component component = new Component();
        private bool disposed = false;
        #endregion

        #region "Metodos"

        public string retRegEdit()
        {
            string strUser = string.Empty;
            string strPassword = string.Empty;
            string strSource = string.Empty;
            string strConexion = string.Empty;
            try
            {
                strUser = Convert.ToString(ConfigurationManager.AppSettings["User"].ToString());
                strPassword = Convert.ToString(ConfigurationManager.AppSettings["Password"].ToString());
                strSource = Convert.ToString(ConfigurationManager.AppSettings["Source"].ToString());
                //kConexion = "Data source=" + xKey_Server + ";Initial Catalog=" + xKey_Bd + ";User ID=" + xKey_User + ";Password=" + xKey_Pwd + ";Connect Timeout=3600;Encrypt=False";
                strConexion = "Password=" + strPassword + ";User ID=" + strUser + ";Data Source=" + strSource + ";Persist Security Info=True";
            }
            catch (Exception lexcError)
            {
                throw new Exception(lexcError.Message);
            }
            finally
            {
                //objSeguridad = null;
            }
            return strConexion;
        }

        public string retStrConexion()
        {
            string strConexion = string.Empty;
            try
            {
                strConexion = ConfigurationManager.ConnectionStrings["HECO"].ConnectionString;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return strConexion;
        }

        public OracleConnection retConexion()
        {
            return conexion;
        }

        public void retOpen()
        {
            if (conexion == null)
            {
                conexion = new OracleConnection(retStrConexion());

                if (conexion.State != ConnectionState.Open)
                {
                    try
                    {
                        conexion.Open();
                    }
                    catch (Exception lexcError)
                    {
                        throw new Exception(lexcError.Message);
                    }
                }
            }
        }

        public void retClose()
        {
            conexion.Close();
            conexion.Dispose();
        }

        #endregion

        #region Devolver conjunto de registros en un DataTable

        public DataTable getTable(OracleCommand cmd)
        {
            DataTable dtt = new DataTable();
            try
            {
                retOpen();
                cmd.Connection = conexion;

                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    dtt.Load(reader, LoadOption.OverwriteChanges);
                    reader.Close();
                }
                retClose();
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            return dtt;
        }

        #endregion

        #region Ejecutar Command

        public int ejecutaSQL(OracleCommand cmd)
        {
            int res = 0;
            try
            {
                retOpen();
                cmd.Connection = conexion;
                res = cmd.ExecuteNonQuery();
                retClose();
            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            return res;
        }
        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    component.Dispose();
                }
                disposed = true;

            }
        }

        #endregion
    }

}
